package com.example.mapp.buildSql;

import com.example.entity.SpotObjectCensus;
import com.example.entity.SpotObjectPlan;
import com.example.util.StringUtil;

import java.util.Map;

/**
 * 监管场所自定义查询导出
 */
public class SpotObjectPlanMappSql {
    /**
     *
     * 查询的SQL
     * findList
     * @param
     * @return
     */
    public String findlists(SpotObjectPlan spotObjectPlan){
        StringBuffer sb = new StringBuffer();
        sb.append(" select ");
        sb.append("  id AS id, ");
        sb.append("  plan_id AS 'planId', ");
        sb.append("  implement_object AS 'implementObject', ");
        sb.append("  head_man AS 'headMan', ");
        sb.append("  create_by AS 'createBy', ");
        sb.append("  create_date AS 'createDate', ");
        sb.append("  update_by AS 'updateBy', ");
        sb.append("  update_date AS 'updateDate',");
        sb.append("  check_man AS 'checkMan',");
        sb.append("  plan_status AS 'planStatus',");
        sb.append("  check_record AS 'checkRecord',");
        sb.append("  user_id AS 'userId',");
        sb.append("  del_flag AS 'delFlag'");
        sb.append("  from spot_object_plan  where del_flag='1' ");
        if(spotObjectPlan.getId() !=null){
            sb.append(" and id="+spotObjectPlan.getId());
        }
        if(spotObjectPlan.getPlanId() !=null){
            sb.append(" and plan_id="+spotObjectPlan.getPlanId());
        }
        if(StringUtil.isNotBlank(spotObjectPlan.getImplementObject())){
            sb.append(" and implement_object = '"+spotObjectPlan.getImplementObject()+"'");
        }
        if(spotObjectPlan.getImplementObjectId() != null){
            sb.append(" and implement_object_id = '"+spotObjectPlan.getImplementObjectId()+"'");
        }
        if(spotObjectPlan.getUserId() !=null){
            sb.append(" and user_id = '"+spotObjectPlan.getUserId()+"'");
        }
            sb.append(" order by create_date desc ");
        return sb.toString();
    }

    /**
     *
     * 查询维保记录App
     *
     */
    public String findRecordApp(SpotObjectPlan spotObjectPlan){
        StringBuffer sb = new StringBuffer();
        sb.append(" select ");
        sb.append("  che.plan_name as 'planName',");
        sb.append("  che.create_date as 'createDate', ");
        sb.append("  obj.implement_object as 'implementObject',");
        sb.append("  obj.implement_object_id as 'implementObjectId',");
        sb.append("  obj.check_man as 'checkMan',");
        sb.append("  obj.head_man as 'headMan',");
        sb.append("  obj.user_id as 'userId',");
        sb.append("  obj.id as 'objId',");
        sb.append("  obj.plan_id as 'planId',");
        sb.append("  obj.check_record as 'checkRecord' ");

        sb.append("  from spot_object_plan obj ");
        sb.append("  left join spot_check_plan che ON obj.plan_id = che.id ");
        sb.append("  where");
        sb.append("  obj.del_flag='1' and che.del_flag='1' ");
        if(spotObjectPlan.getCheckRecord() !=null){
            sb.append("  and obj.check_record ="+spotObjectPlan.getCheckRecord());
        }
        if(spotObjectPlan.getUserId() !=null){
            sb.append("  and obj.user_id ="+spotObjectPlan.getUserId());
        }
        sb.append(" order by obj.create_date ");
        return sb.toString();
    }

    /**
     *
     * 维保质量统计
     *
     */
    public String spotObjectCensus(Map map) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select ");
        sb.append("  us.planId as 'planId',");
        sb.append("  us.elevatorId as 'elevatorId' , ");
        sb.append("  us.objId as 'objId' ,");
        sb.append("  plan.check_man as 'checkMan' ,");
        sb.append("  plan.implement_object as 'implementObject' ,");
        sb.append("  us.createDate     as 'createDate' ,");
        sb.append("  us.signPhoto     as 'signPhoto' ,");
        sb.append("  (case when  us.resultEvaluate ='1' then '良' when us.resultEvaluate ='0' then '优' when  us.resultEvaluate ='2' then '差' end) as 'resultEvaluate',");
        sb.append("  che.plan_name as 'planName' ");

        sb.append("  FROM ( SELECT sign_photo  As 'signPhoto', create_date AS 'createDate',result_evaluate AS 'resultEvaluate', plan_id AS 'planId',elevator_id AS 'elevatorId',obj_id AS 'objId',user_id as 'userId' FROM spot_object_census GROUP BY plan_id,elevator_id,obj_id,user_id) AS us ");
        sb.append("  LEFT JOIN spot_object_plan plan ON plan.id = us.objId ");
        sb.append("  LEFT JOIN spot_check_plan che ON che.id = us.planId");
        sb.append("  LEFT JOIN party_user pu ON us.userId=pu.user_id");
        sb.append("  where 1=1 and pu.party_id=" + map.get("partyId") + "");

        if(map.get("implementObject") !=null && map.size()>0 ){
            sb.append("  and plan.implement_object='"+map.get("implementObject")+"'");
        }
        if(map.get("startDate") !=null && StringUtil.isNotBlank((String)(map.get("startDate")))){
            sb.append("  and date_format(us.createDate, '%Y%m%d%H%i%s') >='"+map.get("startDate")+"'");
        }

        if(map.get("endDate") !=null && StringUtil.isNotBlank((String)(map.get("endDate")))){
            sb.append("  and date_format(us.createDate, '%Y%m%d%H%i%s') <='"+map.get("endDate")+"'");
        }

        return sb.toString();
    }

    /**
     *
     *  获取已经完成的检查项
     *
     */
    public String getFinishItem(Map map) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select ");
        sb.append("  check_item_name as 'checkItemName',");
        sb.append("  check_record as 'checkRecord',");
        sb.append("  remarkes as 'remarkes',");
        sb.append("  pic_url as 'picUrl',");
        sb.append("  (case when ite.ext2 is not null then ite.ext2 else '自定义维保' end)                as 'ext2',");
        sb.append("  elevator_id as 'elevatorId'");
        sb.append("  from spot_object_census us");
        sb.append("  left join check_item ite ON us.check_item_id = ite.id ");
        sb.append("  where obj_id="+map.get("objId"));
        return sb.toString();
    }

    /**
     * 获取web端导出excel数据
     */
    public String findExcelData(SpotObjectCensus spotObjectCensus) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select ");
        sb.append("  plan.plan_name as 'planName',");
        sb.append("  us.check_item_name as 'checkItemName',");
        sb.append("  case when us.check_record='1' then '否' when us.check_record='0' then '是' else '无此项' end  as 'checkRecord',");
        sb.append("  us.elevator_id as 'elevatorId',");
        sb.append("  us.check_man as 'checkName',");
        sb.append("  us.implement_object as 'implementObject',");
        sb.append("  us.head_man as 'headName' ");

        sb.append("  from spot_object_census us ");
        sb.append("  left join spot_check_plan plan ON us.plan_id = plan.id ");
        sb.append("  where 1=1 ");
        if(spotObjectCensus.getObjId() !=null){
            sb.append("  and us.obj_id="+spotObjectCensus.getObjId());
        }
        if(spotObjectCensus.getPlanId() !=null){
            sb.append("  and us.plan_id="+spotObjectCensus.getPlanId());
        }
        if(StringUtil.isNotBlank(spotObjectCensus.getElevatorId())){
            sb.append("  and us.elevator_id='"+spotObjectCensus.getElevatorId()+"'");
        }

        return sb.toString();
    }


}
